from pymysql import connect, Error
from pymysql.cursors import DictCursor

# from pymysql.cursors import DictCursor

# try:
#     # 建立连接
#     conn = connect(
#         host='127.0.0.1',
#         port=3306,
#         user='root',
#         password='123456',
#         database='my_news'
#     )
#     # 拿到游标对象
#     cursor = conn.cursor()
#     # CRUD
#
#     # 关闭游标
#     cursor.close()
#     print(conn)
# except Error as e:
#     print('连接失败：{}'.format(e))
# finally:
#     try:
#         # 关闭连接
#         conn.close()
#         print('数据库连接已关闭')
#     except Error as e:
#         print('数据库连接关闭失败：{}'.format(e))

# 用with语法改造
# with语法的好处就是打开了 可以自动关闭
try:
    # 建立连接
    conn = connect(
        host='127.0.0.1',
        port=3306,
        user='root',
        password='123456',
        database='my_news',
        # 将结果转换为字典
        cursorclass=DictCursor
    )
    with conn:
        with conn.cursor() as cursor:
            # sql = 'SELECT * FROM school_student_info;'
            # 查询带条件的sql语句
            # class_name = '三年级一班'
            # stu_name = '王五'
            # 这种写法有安全隐患  会有恶意拼接sql的风险
            # sql = 'SELECT * FROM school_student_info where class_name= "' + class_name + '";'

            # 防止SQL注入解决方案  使用%s占位符，参数另外传递
            # sql = 'SELECT * FROM school_student_info where class_name= %s;'
            # cursor.execute(sql, ('三年级一班',))
            # cursor.execute(sql, (class_name,))

            sql = 'SELECT * FROM school_student_info where class_name= %s and stu_name=%s;'
            cursor.execute(sql, ('三年级一班', '王五',))
            # cursor.execute(sql, (class_name, stu_name,))

            # # 执行SQL
            # cursor.execute(sql)

            # 查询
            # 方式一：获取查询的结果(所有)
            # for item in cursor:
            #     print(item)
            # 方式二：检索一条数据
            # one_data = cursor.fetchone()
            # print(one_data)
            # two_data = cursor.fetchone()
            # print(two_data)

            print('--------------------------------')
            # 方式三：检索全部数据
            # all_data = cursor.fetchall()
            # # print(all_data)
            # for item in all_data:
            #     print(item)
            # 方式四：检索指定数量的数据
            data_list = cursor.fetchmany(5)
            # print(data_list)
            for item in data_list:
                # 获取所有字段的信息
                print(item)
                # 获取姓名（元组的使用方法）
                # print(item[2])
                # 转换为字典之后的使用
                # 获取姓名（字典的使用方法）
                print(item['stu_name'])


except Error as e:
    print('连接失败：{}'.format(e))
